from sqlalchemy import Column, Integer, String, text
from sqlalchemy.dialects.mysql import LONGTEXT

from models.base import Base, db


class Paragraphs(Base):
    __tablename__ = 'paragraphs'
    id = Column(Integer, primary_key=True, autoincrement=True)
    order_number = Column(String(30), nullable=False)
    paragraph_index = Column(Integer, nullable=False)
    content = Column(LONGTEXT, default='')

    @classmethod
    def get_indexs(cls, order_number):
        sql = "select paragraph_index from paragraphs where order_number = '%s'" % order_number
        result = db.execute(sql).fetchall()
        return result

    def get_paragraphs(self, order_number):
        paragraphs = db.query(Paragraphs).filter_by(order_number=order_number).all()
        res = []
        for item in paragraphs:
            res.append({
                'content': self.format(item.content),
                'edit': 0
            })
        return res

    def format(self, data):
        try:
            str = data.strip().replace('\\', '\\\\').replace('\'', '\\\''). \
                replace('\"', '\\\"').replace('\r', '').replace('\r', '') \
                .replace('\n', '').replace('\t', '').replace('\r', '').replace('\f', '').replace('\v', '')
        except:
            str = ''
        return str

    def insert_paragrahs(self, paragraphs, order_number):
        params = []
        index = 0

        for paragraph in paragraphs:
            if paragraph == '':
                paragraph = ' '
            params.append(
                {
                    'order_number': order_number,
                    'paragraph_index': index,
                    'content': paragraph
                }
            )
            index += 1
        # sql = "insert into paragraphs VALUES (%s,%s,%s)"%('order_number','paragraph_index','content')
        db.execute(Paragraphs.__table__.insert(), params)


if __name__ == '__main__':
    params = [
        {'order_number': '1', 'paragraph_index': 1, 'content': '2313213'},
        {'order_number': '2', 'paragraph_index': 2, 'content': '2313213'},
        {'order_number': '3', 'paragraph_index': 3, 'content': '2313213'},
    ]
    # params.append({})
    s = Paragraphs()
    s.insert_paragrahs(params)
